package com.tarena.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.tarena.entity.Emp;
import com.tarena.util.datasource.DBConnection;

public class EmpDao {
	public List<Emp> findAllEmp() throws Exception{
		List<Emp> emps = new ArrayList<Emp>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs =null;
		try {
			conn=DBConnection.getConnection();
			String sql = "select * from fuck_emp";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				Emp emp = new Emp();
				emp.setEmpId(rs.getInt(1));
				emp.setEmpName(rs.getString(2));
				emp.setSalary(rs.getDouble(3));
				emps.add(emp);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("查询失败");
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return emps;
	}
	
	public Emp findEmpById(int id)throws Exception{
		Emp emp = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn=DBConnection.getConnection();
			String sql = "select * from fuck_emp where emp_id = ?";
			ps =conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()){
				emp=new Emp();
				emp.setEmpId(rs.getInt(1));
				emp.setEmpName(rs.getString(2));
				emp.setSalary(rs.getDouble(3));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("查询失败");
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return emp;
	}
	
	public int deleteEmpById(int id) throws Exception{
		int result =0;
		Connection conn = null;
		PreparedStatement ps =null;
		try {
			conn= DBConnection.getConnection();
			String sql = "delete from fuck_emp where emp_id = ?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			result=ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("删除失败");
		}finally{
			DBConnection.close(ps, conn);
		}
		return result;
	}
	
	public int addEmp(Emp emp) throws Exception{
		int result = 0;
		Connection conn =null;
		PreparedStatement ps =null;
		try {
			conn=DBConnection.getConnection();
			String sql = "insert into fuck_emp values(?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, emp.getEmpId());
			ps.setString(2,emp.getEmpName());
			ps.setInt(3, (int) emp.getSalary());
			result=ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("添加失败");
		}finally{
			DBConnection.close(ps, conn);
		}
		return result;
	}
	
	public int updateEmp(Emp emp)throws Exception{
		int result = 0;
		Connection conn =null;
		PreparedStatement ps =null;
		try {
			conn=DBConnection.getConnection();
			String sql = "update fuck_emp set emp_name=?,salary=? where emp_id=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(3, emp.getEmpId());
			ps.setString(1,emp.getEmpName());
			ps.setInt(2, (int) emp.getSalary());
			result=ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("修改失败");
		}finally{
			DBConnection.close(ps, conn);
		}
		return result;
	}
	
	public static void main(String[] args){
		EmpDao empDao = new EmpDao();
		try {
//			List<Emp> emps = empDao.findAllEmp();
//			for (Emp emp : emps) {
//				System.out.println(emp.getEmpId());
//				System.out.println(emp.getEmpName());
//				System.out.println(emp.getSalary());
//			}
//			Emp emp2 = empDao.findEmpById(4);
//			System.out.println(emp2.getEmpId());
//			System.out.println(emp2.getEmpName());
//			System.out.println(empDao.deleteEmpById(3));
			Emp emp = new Emp();
			emp.setEmpId(104);
			emp.setEmpName("oop");
			emp.setSalary(5000);
//			System.out.println(empDao.addEmp(emp));
			System.out.println(empDao.updateEmp(emp));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
